ExcelVBAPartsCollection Home Excel Reference Manual DownLoad My Profile
Menu Back Next Links Excel Function Manual Myself My BBS


セルの書式設定 (2)

　
シートの保護
条件付書式
入力規則




シートの保護


1　シート保護(Protect メソッド)の構文と意味

expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)
expression では、保護するワークシートを指定します。
引　　　数 指　　　　　定　　　　　内　　　　　容
Password　 省略可能です。パスワード文字列を指定します。なお、パスワードでは大文字と小文字を区別します。この引数を省略すると、シートまたはブックはパスワードの使用なしで保護解除できます。保護解除をするためには、そのパスワードが必要になります。
DrawingObjects　 省略可能です。描画オブジェクトを保護させるには、True（既定値）を指定します。既定値は False です。　
Contents　 省略可能です。オブジェクト(セル)の内容を保護させるには、True（既定値）を指定します。この引数による保護対象は、グラフの場合はグラフ全体、ワークシートの場合はセルです。既定値は True です。　
Scenarios　 省略可能です。シナリオを保護するには、True（既定値）を指定します。この引数はワークシートの場合のみ有効です。　
UserInterfaceOnly　 省略可能です。True を指定すると、画面上からの変更は保護されますが、マクロからの変更は保護されません。この引数を省略すると、マクロからも、画面上も変更することができなくなります。

2　シートを保護する

シート保護の種類と指定方法

処理内容：シート全体を保護します。
Sub protect_1()
With Worksheets("Sheet1")
.Unprotect
.Protect DrawingObjects:=True, Contents:=True,UserInterfaceOnly:=True
.Protect
End With
End Sub

処理内容：数式が入力されているセルだけを保護します。
Sub protect_2()
Worksheets("Sheet1").Select
Cells.select
With Selection
.Locked = False
.Specialcells(xlCellTypeFormulas).Locked = True
End With
ActiveSheet.Protect
End Sub

処理内容：セル　A1:G3 のみ書き込み可能とし、他のセルを保護します。
Sub protect_3()
With Worksheets("Sheet1")
.Range("A1:G3").Locked = False
.protect
End With
End Sub

3　保護の解除

シート保護の解除

処理内容：シートの保護を解除します。
Sub protect_4()
Worksheets("Sheet1").Unprotect
End Sub




条件付書式


1　条件付書式の構文と意味

　条件付書式で設定できる条件設定方法には、「セルの値」と「数式」の 2 種類があり、指定できる書式は、フォント、罫線、セルの背景色・塗りつぶし(パターン)です。なお、条件付書式は 1 つの範囲に対して 3 つまでしか指定できません。条件付書式の詳細はここをクリックして下さい。

(1) 条件式を設定する構文
　　expression.FormatConditions Add(Type, Operator, Formula1, Formula2)
　　expression では、条件付書式を設定する範囲を指定します。
　
(2) 書式を設定する構文
　　expression.FormatConditions(番号) <フォント、セルの背景色・塗りつぶし、罫線の書式の指定>
　　 expression では、条件付書式を設定する範囲を指定します。
　　ExcelVBAでフォント等の指定を行うにはここをクリックして下さい。

引　数 指　　　　　定　　　　　内　　　　　容
Type 必ず指定します。「条件」で、「セルの値が」のときは、xlCellValue を、「数式が」のときは、xlExpression を指定します。
Type で xlExpression を指定したときは、下記のOperatorは使用できませんので(無効になる)、Formula1 に条件式を指定します。
Operator　 省略可能ですType で xlCellValue を指定したときの条件式を下記の組込定数で指定します。
「値」は比較するセルの値、「F1」は Formula1、「F2」は Formula2 を示します。
組込定数 演算子表記 意　　　　　味
xlBetween 値>=F1 And 値<=F2 値がFormula1とFormula2の間
xlEqual 値=F1 値がFormula1と等しい
xlGreater 値>F1 値がFormula1より大きい
xlGreaterEqual 値>=F1 値がFormula1以上
xlLess 値<F1 値がFormula1より小さい
xlLessEqual 値<=F1 値がFormula1以下
xlNotBetween 値 Not(=>F1 And 値<=F2) 値がFormula1とFormula2の間以外
xlNotEqual 値<>F1 値がFormula1と異なる
Formula1 省略可能です。条件付き書式に関連させる値またはオブジェクト式を指定します。定数値、文字列値、セル参照、または数式を指定できます。
Type で xlExpression を指定したときは、Operator の代わりに Fomula1 で数式を指定します。
Formula2 省略可能です。Operator に xlBetween または xlNotBetween を指定した場合、条件付き書式の 2 番目の部分に関連させる値またはオブジェクト式を指定します。それ以外を指定した場合、この引数は無視されます。定数値、文字列値、セル参照、または数式を指定できます。




2　セルの値による条件付書式

　Excel メニューの「書式(O)」「条件付書式(D)」「条件付き書式の設定」ダイアログボックスにある「条件」で「セルの値が」の方法により条件付書式を設定する場合の処理です。
「セルの値が」による条件付書式

処理内容：入力値が10から20までのとき、文字フォント=青、背景色=黄色に設定します。
Sub FormatConditions_1()
Worksheets("Sheet1").Select
Range("A1:A10").Select
With Selection
With .FormatConditions
.Delete
.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="10", Formula2:="20"
End With
With .FormatConditions(1)
.Font.ColorIndex = 5
.Interior.ColorIndex = 6
End With
End With
End Sub

処理内容：文字フォントを入力値が90〜139 = 黄色、140〜159 = 水色、160〜 赤色に設定します。
Sub FormatConditions_2()
Worksheets("Sheet1").Select
Range("A1:A10").Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="90", Formula2:="139"
.FormatConditions(1).Font.Color = RGB(255, 255, 0)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="140", Formula2:="159"
.FormatConditions(2).Font.Color = RGB(0, 255, 255)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, _
Formula1:="160"
.FormatConditions(3).Font.Color = RGB(255, 0, 0)
End With
End Sub




3　数式による条件付書式

　Excel メニューの「書式(O)」「条件付書式(D)」「条件付き書式の設定」ダイアログボックスにある「条件」で「数式が」の方法により条件付書式を設定する場合の処理です。
「数式が」による条件付書式

処理内容：A列,B列の値がともに 50 以上のとき、C1:C10の背景色をピンク色に設定します。
Sub FormatConditions_3()
Worksheets("Sheet1").Select
Range("C1:C10").FormatConditions.Delete
Range("C1").Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(A1>=50,B1>=50)"
.FormatConditions(1).Interior.ColorIndex = 38
.Copy
End With
Range("C2:C10").PasteSpecial Paste:=xlFormats
End Sub

処理内容：文字フォントをA1:A10の曜日が日曜のとき赤色、土曜のとき水色に設定します。
Sub FormatConditions_4()
Worksheets("Sheet1").Select
Range("A1:A10").FormatConditions.Delete
Range("A1").Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAY(A1)=1"
.FormatConditions(1).Font.ColorIndex = 7
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAY(A1)=7"
.FormatConditions(2).Font.ColorIndex = 8
.Copy
End With
Range("A2:A10").PasteSpecial Paste:=xlFormats
End Sub




入力規則


1　入力規則の構文と意味

　入力規則は、セルに入力できるデータの種類の設定、入力時に表示するメッセージの表示、入力規則に設定されたデータ以外のデータが入力されたときのエラーメッセージの表示、IMEモードの制御を行う機能です。入力規則の詳細はここをクリックして下さい。

マクロの自動記録機能を使用して初期状態(既定値)の入力規則を見ると次のようになっています。

Sub Macro1 　
With Selection.Validation
 　
.Delete
 '既定の入力規則を削除する　
.Add Type:=xlValidateInputOnly, AlertStyle _
'入力値の種類,エラー発生時の警告アイコン,入力条件演算方法の設定　
:=xlValidAlertStop, Operator:=xlBetween
 　
.IgnoreBlank = True
 '空白を無視する　
.InCellDropdown = True
 'リストボックスから選択する　
.InputTitle = ""
 '入力時メッセージタイトル　
.ErrorTitle = ""
 'エラー時メッセージタイトル　
.InputMessage = ""
 '入力時メッセージ　
.ErrorMessage = ""
 'エラー時メッセージ　
.IMEMode = xlIMEModeNoControl
 'IMEモード　
.ShowInput = True
 '入力規則の設定をコメントで表示　
.ShowError = True
 '入力規則以外のデータ入力時エラー表示　
End With
 　
End Sub 　

　上記コードのうち、Add メソッドの書式の構文の詳細を下記に述べます。
　　　expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)

　espression　では、<入力規則を設定する範囲>.Validation とします。
　Add メソッドの引数の内容を以下に示します。

引　数

指　　　　　定　　　　　内　　　　　容
Type 必ず指定します。入力規則の種類を指定します。

入力規則の種類

内　　　　　容
xlValidateInputOnly すべての値
引数 AlertStyle、引数 Formula1、または引数 Formula2 を使用します。 
xlValidateWholeNumber 整数
xlValidateDecimal 小数点
xlValidateTextLength 文字列（長さ指定）
xlValidateList リスト
引数 Formula1 を必ず指定します。引数 Formula2 は無視されます。引数 Formula1 には、カンマで区切った値の一覧またはこの一覧へのシート参照を指定しなければなりません。 
xlValidateDate 日付
xlValidateTime 時刻
xlValidateCuestcm ユーザー設定
引数 Formula1 を必ず指定します。引数 Formula2 は無視されます。引数 Formula1 には、データ入力が有効な場合は True になり、データ入力が無効な場合は False になる式を指定しなければなりません。
AlertStyle 省略可能です。入力規則でのエラーのスタイルを指定します。

アイコン

内　　　　　容
xlValidAlertStop 停止
xlValidAlertWarning 注意
xlValidAlertInformation 情報
Operator 省略可能です。入力規則での演算子を指定します。
演算子

内　　　　　容
xlBetween Formula1とFormula2の間
xlEqual Formula1と等しい
xlGreater Formula1より大きい
xlGreaterEqual Formula1以上
xlLess Formula1より小さい
xlLessEqual Formula1以下
xlNotBetween Formula1とFormula2の間以外
xlNotEqual Formula1と異なる
Formula1 省略可能です。データの入力規則での条件式の最初の部分を指定します。
Formula2 省略可能です。データの入力規則での条件式の 2 番目の部分を指定します。引数 Operator が xlBetween または xlNotBetween の場合、この引数は無視されます。

　日本語の入力規則を設定するIMEMODE プロパティの書式の構文の詳細を下記に述べます。
　　　expression.IMEMode = <定数>

　espression　では、<入力規則を設定する範囲>.Validation とします。
　IMEMode プロパティの値(定数)を以下に示します。

定　　　　　数 内　　　　　容 
xlIMEModeAlpha 半角英数字 
xlIMEModeAlphaFull 全角英数字 
xlIMEModeDisable IMEは利用禁止状態
xlIMEModeHiragana ひらがな 
xlIMEModeKatakana カタカナ
xlIMEModeKatakanaHalf  カタカナ (半角) 
xlIMEModeNoControl IMEを制御しない（既定値）
xlIMEModeOff  オフ (英語モード) 
xlIMEModeOn オン




2　入力規則設定例

1　リストボックスを利用してデータ入力

処理内容：F列をクリックすると、" りんご,みかん,バナナ"の入力候補を表示します。
Sub Validation_1()
Worksheets("Sheet1").Select
Columns(6).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, Formula1:= " りんご,みかん,バナナ"
End With
End Sub
セルに入力されている値をリストボックスに表示するときは
.Add Type:=xlValidateList, Formula1:="=$C$1:$C$3"
のように入力セル範囲を指定する

2　入力データを一定範囲の整数に制限

処理内容：F列のセルにを 1 から 10 までしか入力できないようにします。
Sub Validation_2()
Worksheets("Sheet1").Select
Columns(6).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="10"
End With
End Sub

3　シート単位の入力制限

処理内容：シートに数値しか入力できないようにします。
Private Sub Worksheet_Change(ByVal Target As Range)
If IsNumeric(Target.Value) = False Then
Application.Undo
End If
End Sub
入力規則を設定するシートモジュールに上記コードを記述します。




Gポイントポイ活　Amazon Yahoo 楽天

無料ホームページ 楽天モバイル[UNLIMITが今なら1円] 海外格安航空券 海外旅行保険が無料！